package io.renren.dao;

import io.renren.common.dao.BaseDao;
import io.renren.entity.MakecardvouchersdEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * ${comments}
 *
 * @author Sen 524227263@qq.com
 * @since 1.0.0 2024-05-30
 */
@Mapper
public interface MakecardvouchersdDao extends BaseDao<MakecardvouchersdEntity> {

    @Select("select * from(\n" +
            "select\n" +
            "MakeCardVouchersM.CardVouchersRuleM_FK,MakeCardVouchersM.GoodsM_FK,/*用于卡券规则穿透*/\n" +
            "MakeCardVouchersD.MakeCardVouchersDOID,/*用于卡券流水*/\n" +
            "MakeCardVouchersM.CardVouchersType,/*卡券类型*/\n" +
            "MakeCardVouchersM.IsDonation,/*能否赠送*/\n" +
            "(case when CardVouchersRuleM.CardNature='1' then '代金卡'\n" +
            " when CardVouchersRuleM.IsMakeDiffPrice='true' then '需补差' else '' end) as CardNature,/*卡券性质*/\n" +
            "MakeCardVouchersD.CardVouchersCode,/*卡券号*/\n" +
            "MakeCardVouchersM.CardVouchersName as CardName,/*卡券名称*/\n" +
            "(case when MakeCardVouchersD.SentFlag = '0' then '未发卡'\n" +
            "when MakeCardVouchersD.SentFlag= '1' then '已发卡'\n" +
            "when MakeCardVouchersD.SentFlag = '2' and (case when convert(varchar(10),MakeCardVouchersD.EndDate,108)='00:00:00'\n" +
            "     then DATEADD(day,1,MakeCardVouchersD.EndDate)\n" +
            "     else MakeCardVouchersD.EndDate end) > GETDATE()\n" +
            "              then '使用中'\n" +
            "when MakeCardVouchersD.SentFlag = '3' then '已用完'\n" +
            "when MakeCardVouchersD.SentFlag = '4' then '已终止'\n" +
            "when MakeCardVouchersD.SentFlag= '5' then '已作废'\n" +
            "when MakeCardVouchersD.SentFlag= '6' then '已冻结'\n" +
            "when MakeCardVouchersD.SentFlag = '2' and (case when convert(varchar(10),MakeCardVouchersD.EndDate,108)='00:00:00' then DATEADD(day,1,MakeCardVouchersD.EndDate)else MakeCardVouchersD.EndDate end) < GETDATE() then '已过期'\n" +
            "else '' end) as SentFlag,/*卡券状态*/\n" +
            "Auto.AutoNo,/*车牌号*/\n" +
            "MakeCardVouchersM.IsAutoUse,\n" +
            "TraderM.Code as TraderCode,/*客户编号*/\n" +
            "TraderM.Name as TraderName,/*客户名称*/\n" +
            "MakeCardVouchersD.StartDate,/*启用日期*/\n" +
            "MakeCardVouchersD.EndDate,/*截止日期*/\n" +
            "CardVouchersRuleM.UseRules/*使用规则*/\n" +
            "from MakeCardVouchersD\n" +
            "left outer join MakeCardVouchersM on MakeCardVouchersD.MakeCardVouchersM_FK=MakeCardVouchersM.MakeCardVouchersMOID\n" +
            "left outer join CardVouchersRuleM on MakeCardVouchersM.CardVouchersRuleM_FK=CardVouchersRuleM.CardVouchersRuleMOID\n" +
            "left outer join TraderM on MakeCardVouchersD.TraderM_FK = TraderM.TraderMOID\n" +
            "left outer join Auto on MakeCardVouchersD.Auto_FK = Auto.AutoOID\n" +
            "where TraderMOID=#{traderMOID} ) t where SentFlag='使用中' ${whereSql}" )
    List<Map<String, Object>> querySql(@Param("traderMOID") String traderMOID, @Param("whereSql") String whereSql);

    @Select("SELECT DISTINCT\n" +
            "\t( CardVouchersRuleM_FK ) AS CardVouchersRuleM_FK \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\tcvrm.CardVouchersRuleMOID AS CardVouchersRuleM_FK,\n" +
            "\t\tisnull(\n" +
            "\t\tCASE\n" +
            "\t\t\t\t\n" +
            "\t\t\t\tWHEN CardNature = 1 THEN\n" +
            "\t\t\t\t'代金卡' ELSE ( SELECT CodeName FROM CodeValue WHERE CodeValue.CodeTableID= 'NewCardVouchersType' AND CodeValue.CodeID= cmMain.CardVouchersType ) \n" +
            "\t\t\tEND,\n" +
            "\t\t\t'项目券' \n" +
            "\t\t) AS CardTypeName,\n" +
            "\t\tcvrm.CardVouchersName AS CardName,\n" +
            "\t\t'' AS CardPic,\n" +
            "\t\tCONVERT ( VARCHAR ( 10 ), cdMain.StartDate, 120 ) AS StartDate,\n" +
            "\t\tCONVERT ( VARCHAR ( 10 ), cdMain.EndDate, 120 ) AS EndDate,\n" +
            "\t\tcvrm.UseRules AS UseRules,\n" +
            "\t\t(\n" +
            "\t\tCASE\n" +
            "\t\t\t\t\n" +
            "\t\t\t\tWHEN cdMain.SentFlag = '0' THEN\n" +
            "\t\t\t\t'未发卡' \n" +
            "\t\t\t\tWHEN cdMain.SentFlag= '1' THEN\n" +
            "\t\t\t\t'已发卡' \n" +
            "\t\t\t\tWHEN cdMain.SentFlag = '2' \n" +
            "\t\t\t\tAND (\n" +
            "\t\t\t\tCASE\n" +
            "\t\t\t\t\t\t\n" +
            "\t\t\t\t\t\tWHEN CONVERT ( VARCHAR ( 10 ), cdMain.EndDate, 108 ) = '00:00:00' THEN\n" +
            "\t\t\t\t\t\tDATEADD( DAY, 1, cdMain.EndDate ) ELSE cdMain.EndDate \n" +
            "\t\t\t\t\tEND \n" +
            "\t\t\t\t\t\t) > GETDATE( ) THEN\n" +
            "\t\t\t\t\t\t'使用中' \n" +
            "\t\t\t\t\t\tWHEN cdMain.SentFlag = '3' THEN\n" +
            "\t\t\t\t\t\t'已用完' \n" +
            "\t\t\t\t\t\tWHEN cdMain.SentFlag = '4' THEN\n" +
            "\t\t\t\t\t\t'已终止' \n" +
            "\t\t\t\t\t\tWHEN cdMain.SentFlag= '5' THEN\n" +
            "\t\t\t\t\t\t'已作废' \n" +
            "\t\t\t\t\t\tWHEN cdMain.SentFlag= '6' THEN\n" +
            "\t\t\t\t\t\t'已冻结' \n" +
            "\t\t\t\t\t\tWHEN cdMain.SentFlag = '2' \n" +
            "\t\t\t\t\t\tAND (\n" +
            "\t\t\t\t\t\tCASE\n" +
            "\t\t\t\t\t\t\t\t\n" +
            "\t\t\t\t\t\t\t\tWHEN CONVERT ( VARCHAR ( 10 ), cdMain.EndDate, 108 ) = '00:00:00' THEN\n" +
            "\t\t\t\t\t\t\t\tDATEADD( DAY, 1, cdMain.EndDate ) ELSE cdMain.EndDate \n" +
            "\t\t\t\t\t\t\tEND \n" +
            "\t\t\t\t\t\t\t\t) < GETDATE( ) THEN\n" +
            "\t\t\t\t\t\t\t\t'已过期' ELSE '' \n" +
            "\t\t\t\t\t\t\tEND \n" +
            "\t\t\t\t\t\t\t) AS SentFlag,\n" +
            "\t\t\t\t\t\t\tau.AutoNo ,\n" +
            "\t\t\t\t\t\t\tmytable.pageNum AS CardNum \n" +
            "\t\t\t\t\t\tFROM\n" +
            "\t\t\t\t\t\t\t(\n" +
            "\t\t\t\t\t\t\tSELECT MAX\n" +
            "\t\t\t\t\t\t\t\t( cd.MakeCardVouchersDOID ) AS carID,\n" +
            "\t\t\t\t\t\t\t\tCOUNT ( 1 ) AS pageNum \n" +
            "\t\t\t\t\t\t\tFROM\n" +
            "\t\t\t\t\t\t\t\tMakeCardVouchersD AS cd\n" +
            "\t\t\t\t\t\t\t\tINNER JOIN MakeCardVouchersM AS cm ON cd.MakeCardVouchersM_FK = cm.MakeCardVouchersMOID \n" +
            "\t\t\t\t\t\t\tWHERE\n" +
            "\t\t\t\t\t\t\t\tcd.TraderM_FK=#{traderMOID} \n" +
            "\t\t\t\t\t\t\t\tAND cd.SentFlag = '2' \n" +
            "\t\t\t\t\t\t\t\tAND (\n" +
            "\t\t\t\t\t\t\t\tCASE\n" +
            "\t\t\t\t\t\t\t\t\t\t\n" +
            "\t\t\t\t\t\t\t\t\t\tWHEN CONVERT ( VARCHAR ( 10 ), cd.EndDate, 108 ) = '00:00:00' THEN\n" +
            "\t\t\t\t\t\t\t\t\t\tDATEADD( DAY, 1, cd.EndDate ) ELSE cd.EndDate \n" +
            "\t\t\t\t\t\t\t\t\tEND \n" +
            "\t\t\t\t\t\t\t\t\t) > GETDATE( ) \n" +
            "\t\t\t\t\t\t\t\t\tAND isnull( cm.CardVouchersRuleM_FK, '00000000-0000-0000-0000-000000000000' ) != '00000000-0000-0000-0000-000000000000' \n" +
            "\t\t\t\t\t\t\t\tGROUP BY\n" +
            "\t\t\t\t\t\t\t\t\tcm.CardVouchersRuleM_FK,\n" +
            "\t\t\t\t\t\t\t\t\tcd.StartDate,\n" +
            "\t\t\t\t\t\t\t\t\tcd.EndDate UNION\n" +
            "\t\t\t\t\t\t\t\tSELECT\n" +
            "\t\t\t\t\t\t\t\t\tISNULL( cd.MakeCardVouchersDOID, '00000000-0000-0000-0000-000000000000' ) AS carID,\n" +
            "\t\t\t\t\t\t\t\t\t1 AS pageNum \n" +
            "\t\t\t\t\t\t\t\tFROM\n" +
            "\t\t\t\t\t\t\t\t\tMakeCardVouchersD AS cd\n" +
            "\t\t\t\t\t\t\t\t\tINNER JOIN MakeCardVouchersM AS cm ON cd.MakeCardVouchersM_FK = cm.MakeCardVouchersMOID \n" +
            "\t\t\t\t\t\t\t\tWHERE\n" +
            "\t\t\t\t\t\t\t\t\tcd.TraderM_FK =#{traderMOID} \n" +
            "\t\t\t\t\t\t\t\t\tAND cd.SentFlag = '2' \n" +
            "\t\t\t\t\t\t\t\t\tAND (\n" +
            "\t\t\t\t\t\t\t\t\tCASE\n" +
            "\t\t\t\t\t\t\t\t\t\t\t\n" +
            "\t\t\t\t\t\t\t\t\t\t\tWHEN CONVERT ( VARCHAR ( 10 ), cd.EndDate, 108 ) = '00:00:00' THEN\n" +
            "\t\t\t\t\t\t\t\t\t\t\tDATEADD( DAY, 1, cd.EndDate ) ELSE cd.EndDate \n" +
            "\t\t\t\t\t\t\t\t\t\tEND \n" +
            "\t\t\t\t\t\t\t\t\t\t) > GETDATE( ) \n" +
            "\t\t\t\t\t\t\t\t\t\tAND isnull( cm.CardVouchersRuleM_FK, '00000000-0000-0000-0000-000000000000' ) = '00000000-0000-0000-0000-000000000000' \n" +
            "\t\t\t\t\t\t\t\t\t) AS mytable\n" +
            "\t\t\t\t\t\t\t\t\tINNER JOIN MakeCardVouchersD AS cdMain ON cdMain.MakeCardVouchersDOID = mytable.carID\n" +
            "\t\t\t\t\t\t\t\t\tINNER JOIN MakeCardVouchersM AS cmMain ON cdMain.MakeCardVouchersM_FK = cmMain.MakeCardVouchersMOID\n" +
            "\t\t\t\t\t\t\t\t\tLEFT JOIN Auto au ON cdMain.Auto_FK= au.AutoOID\n" +
            "\t\t\t\t\t\t\t\tLEFT JOIN CardVouchersRuleM cvrm ON cvrm.CardVouchersRuleMOID= cmMain.CardVouchersRuleM_FK \n" +
            "\t) mytemp1")
    List<Map<String, Object>> querySql2(@Param("traderMOID") String traderMOID);
}